/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.cardapiodigital.data;

import br.com.cardapiodigital.database.ConnectionManager;
import br.com.cardapiodigital.entity.Cidades;
import br.com.cardapiodigital.entity.Estabelecimentos;
import br.com.cardapiodigital.exception.CardapioDigitalException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author egberto
 */
public class EstabelecimentosData extends GenericData<Estabelecimentos> {

    @Override
    public void salvar(Estabelecimentos entity) throws CardapioDigitalException {
        PreparedStatement pstmt = null;
        Connection conn = null;
        
        String sql = "INSERT INTO estabelecimentos " +
                "(cnpj,nomefantasia,razaosocial,endereco,bairro,cep," +
                "email,link,titular,status,cidades_id,login,senha,obs )" +
                " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";    
        
        try{
            conn = ConnectionManager.getConexao(); 
            pstmt = conn.prepareStatement(sql);
            // preenche os valores ?,?
            pstmt.setString(1, entity.getCnpj());
            pstmt.setString(2, entity.getNomefantasia());
            pstmt.setString(3, entity.getRazaosocial());           
            pstmt.setString(4, entity.getEndereco());
            pstmt.setString(5, entity.getBairro());
            pstmt.setString(6, entity.getCep());
            pstmt.setString(7, entity.getEmail());
            pstmt.setString(8, entity.getLink());
            pstmt.setString(9, entity.getTitular());
            pstmt.setInt(10, entity.getStatus());
            pstmt.setInt(11, entity.getCidadesId().getId());
            pstmt.setString(12, entity.getLogin());
            pstmt.setString(13, entity.getSenha());
            pstmt.setString(14, entity.getObs());
            
            pstmt.execute();
            
        }catch(SQLException ex){
            Logger.getLogger(EstabelecimentosData.class.getName()).
                    log(Level.SEVERE, null, ex);
            String errorMsg = "Erro na execucao da query" + sql;
            throw new CardapioDigitalException(errorMsg, ex);
        }finally{
            ConnectionManager.close(conn, pstmt);
        }
    }

    @Override
    public void atualizar(Estabelecimentos entity) throws CardapioDigitalException {
        PreparedStatement pstmt = null;
        Connection conn = null;
        
        String sql = "UPDATE estabelecimentos SET cnpj = ?,nomefantasia = ?," +
                "razaosocial = ?,endereco = ?,bairro = ?,cep = ?,email = ?," +
                "link = ?,titular = ?,status = ?,cidades_id = ?,login = ?," +
                "senha = ?,obs = ? WHERE id = ?";    
        
        try{
            conn = ConnectionManager.getConexao(); 
            pstmt = conn.prepareStatement(sql);
            // preenche os valores ?,?
            pstmt.setString(1, entity.getCnpj());
            pstmt.setString(2, entity.getNomefantasia());
            pstmt.setString(3, entity.getRazaosocial());           
            pstmt.setString(4, entity.getEndereco());
            pstmt.setString(5, entity.getBairro());
            pstmt.setString(6, entity.getCep());
            pstmt.setString(7, entity.getEmail());
            pstmt.setString(8, entity.getLink());
            pstmt.setString(9, entity.getTitular());
            pstmt.setInt(10, entity.getStatus());
            pstmt.setInt(11, entity.getCidadesId().getId());
            pstmt.setString(12, entity.getLogin());
            pstmt.setString(13, entity.getSenha());
            pstmt.setString(14, entity.getObs());   
            pstmt.setInt(15, entity.getId());  
            
            pstmt.execute();
            
        }catch(SQLException ex){
            Logger.getLogger(EstabelecimentosData.class.getName()).
                    log(Level.SEVERE, null, ex);
            String errorMsg = "Erro na execucao da query" + sql;
            throw new CardapioDigitalException(errorMsg, ex);
        }finally{
            ConnectionManager.close(conn, pstmt);
        }
    }

    @Override
    public void excluir(int entityId) throws CardapioDigitalException {
        PreparedStatement pstmt = null;
        Connection conn = null;
        
        String sql = "UPDATE estabelecimentos SET status = 0 WHERE id = ?";    
        
        try{
            conn = ConnectionManager.getConexao(); 
            pstmt = conn.prepareStatement(sql);
            // preenche os valores ?,?
            pstmt.setInt(1, entityId);           
            
            pstmt.execute();
            
        }catch(SQLException ex){
            Logger.getLogger(EstabelecimentosData.class.getName()).
                    log(Level.SEVERE, null, ex);
            String errorMsg = "Erro na execucao da query" + sql;
            throw new CardapioDigitalException(errorMsg, ex);
        }finally{
            ConnectionManager.close(conn, pstmt);
        }
    }

    @Override
    public List<Estabelecimentos> buscarTodos() throws CardapioDigitalException {
        String sql = "select * from estabelecimentos order by cidades_id,razaosocial asc";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Estabelecimentos> listaEstab = new ArrayList<Estabelecimentos>();
        
        try{
            conn = ConnectionManager.getConexao();
            stmt =  conn.createStatement();
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                int id = rs.getInt("id");
                String cnpj = rs.getString("cnpj");
                String nomeFantasia = rs.getString("nomefantasia");
                String razaoSocial = rs.getString("razaosocial");
                String endereco = rs.getString("endereco");
                String bairro = rs.getString("bairro");
                String cep = rs.getString("cep");
                String email = rs.getString("email");
                String link = rs.getString("link");
                String titular = rs.getString("titular");
                int status = rs.getInt("status");
                int cidade_id = rs.getInt("cidades_id");
                String login = rs.getString("login");
                String senha = rs.getString("senha");
                String obs = rs.getString("obs");  
                Cidades c = new CidadesData().obtem(cidade_id);
                listaEstab.add(new Estabelecimentos(id,cnpj,nomeFantasia, razaoSocial, 
                                   endereco, bairro, cep, email, link, titular, status, 
                                   c,login, senha,obs));
            }
            
            return  listaEstab;
        
        }catch(SQLException ex){
            Logger.getLogger(EstabelecimentosData.class.getName()).
                    log(Level.SEVERE, null, ex);
            String errorMsg = "Erro na execucao da query" + sql;
            throw new CardapioDigitalException(errorMsg, ex);
        }finally{
            ConnectionManager.close(conn,stmt,rs);
        }
    }

    @Override
    public Estabelecimentos obtem(int entityId) throws CardapioDigitalException {
         String sql = "SELECT * FROM estabelecimentos WHERE id='" + entityId + "'";    

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Estabelecimentos estab = null;
        
        try{
            conn = ConnectionManager.getConexao();
            stmt =  conn.createStatement();  
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
               int id = rs.getInt("id");
                String cnpj = rs.getString("cnpj");
                String nomeFantasia = rs.getString("nomefantasia");
                String razaoSocial = rs.getString("razaosocial");
                String endereco = rs.getString("endereco");
                String bairro = rs.getString("bairro");
                String cep = rs.getString("cep");
                String email = rs.getString("email");
                String link = rs.getString("link");
                String titular = rs.getString("titular");
                int status = rs.getInt("status");
                int cidade_id = rs.getInt("cidades_id");
                String login = rs.getString("login");
                String senha = rs.getString("senha");
                String obs = rs.getString("obs");              
                
                estab = new Estabelecimentos(id,cnpj,nomeFantasia, razaoSocial, 
                                   endereco, bairro, cep, email, link, titular, status, 
                                   new Cidades(cidade_id),login, senha,obs);
            }
            
            return estab;
        
        }catch(SQLException ex){
            Logger.getLogger(EstabelecimentosData.class.getName()).
                    log(Level.SEVERE, null, ex);
            String errorMsg = "Erro na execucao da query " + sql;
            throw new CardapioDigitalException(errorMsg, ex);
        }finally{
            ConnectionManager.close(conn,stmt,rs);
        }
    }
    
    
    
}
